<?php

namespace app\admin\controller;

use cmf\controller\AdminBaseController;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use think\Db;
use think\db\Query;

class StudentController extends AdminBaseController
{
    /**
     * 显示资源列表
     *
     * @return \think\Response
     */
    public function index()
    {
        $school_id = $this->request->param('school_id');
        $school=Db::name('school')->select();
        $room_id=$this->request->param('room_id');
        $room=Db::name('room')->where('sid',$school_id)->select();
        $name=$this->request->param('name');
        $student=Db::name('student')
            ->join('cmf_room','cmf_student.cid=cmf_room.id')
            ->join('cmf_school','cmf_school.id=cmf_room.sid')
            ->where(function (Query $query) use ($school_id,$room_id,$name){
                if($school_id){
                    $query->where('cmf_school.id',"=","$school_id");
                }
                if($room_id){
                    $query->where('cmf_room.id',"=","$room_id");
                }
                if($name){
                    $query->where('cmf_student.name','like',"%$name%");
                }
            })
            ->field('cmf_student.*,cmf_room.name as room_name,cmf_school.name as school_name')
            ->order('cmf_student.id DESC')
            ->paginate('3',false,['query'=>$this->request->param()]);
        $student->appends(['cmf_student.name'=>$name,'school_id'=>$school_id,'room_id'=>$room_id]);
        $page=$student->render();
        return $this->fetch('',[
            'student'=>$student,
            'school'=>$school,
            'room'=>$room,
            'school_id'=>$school_id,
            'room_id'=>$room_id,
            'page'=>$page
            ]);
    }

    public function getRoom()
    {
        //获取当前school_id
        $schoolId=$this->request->post();
        //查询此学校下的班级
        $room=Db::name('room')->where(['sid'=>$schoolId])->select();
        $options=["<option value=''>--请选择班级--</option>"];
        //如果有班级就遍历输出
        if ($room){
            foreach ($room as $v){
                $opt='<option value="'.$v['id'].'">'.$v['name'].'</option>';
                //向$options中添加$opt的值
                array_push($options,$opt);
            }
            //返回值
            return $options;
        }else{
            return ["<option value=''>暂无数据</option>"];
        }
    }

    /**
     * 显示添加资源表单页.
     *
     * @param  int  $id
     * @return \think\Response
     */
    public function add()
    {
        $school=Db::name('school')->select();
        $room=Db::name('room')->select();
        return $this->fetch('student_add',['school'=>$school,'room'=>$room]);
    }
    public function addPost()
    {
        $data=input();
        //halt($data);
        if(request()->isPost()){
            if(empty($data['name'])){
                $this->fetch('学生姓名不能为空');
            }
            $data['create_time']=date('Y-m-d H:i:s',time());
            $data['update_time']=date('Y-m-d H:i:s',time());
            //删除图片变量
            unset($data['photo_name']);
            unset($data['school_id']);
            $add=Db::name('student')->insert($data);
            if($add){
                $this->success('添加成功','student/index');
            }else{
                $this->error('添加失败');
            }
        }
    }

   
    /**
     * 显示编辑资源表单页.
     *
     * @param  int  $id
     * @return \think\Response
     */
    public function edit($id)
    {
        $school=Db::name('school')->select();
        $student=Db::name('student')->get($id);
        $schoolId=Db::name('room')->where('id',$student["cid"])->value('sid');
        //查询班级表中的school_id的值
        $room =Db::name('room')->where('sid',$schoolId)->select();
        return $this->fetch('student_edit',[
            'student'=>$student,
            'school_id'=>$schoolId,
            'school'=>$school,
            'room'=>$room
            ]);
    }
    public function editPost()
    {
        $data = input();
        $data['create_time'] = date('Y-m-d H:i:s',time());
        $data['update_time'] = date('Y-m-d H:i:s',time());
        unset($data['photo_name']);
        unset($data['school_id']);
        $edit = Db::name('student')->update($data);
        if ($edit)
            $this->success('修改成功','student/index');
            $this->error('修改失败');
    }

    public function grade($id)
    {
        $grade = Db::name('grade g')
            ->join('cmf_subject s','s.id=g.subject_id')
            ->join('cmf_student st','st.id=g.student_id')
            ->join('cmf_room r','st.cid=r.id')
            ->field('g.*,s.name as subject_name,st.name as student_name,r.name as room_name')
            ->where('g.id',$id)
            ->select();
        //halt($grade);
        return $this->fetch('',['grade'=>$grade]);
    }


    /**
     * 删除指定资源
     *
     * @param  int  $id
     * @return \think\Response
     */
    public function delete($id)
    {
        $del=Db::name('student')->where('id',$id)->delete();
        if($del){
            $this->success('删除成功','student/index');
        }
            $this->error('删除失败');
    }

    public function delAll()
    {
        $ids = input('ids/a');
        $del = Db::name('student')->delete($ids);
        if ($del){
            $this->success('删除成功','student/index');
        }else{
            $this->error('删除失败');
        }
    }

    public function exportExcel()
    {
        // 查询要导出的数据
        $data = Db::name('student')
            ->join('cmf_room', 'cmf_student.cid=cmf_room.id', 'LEFT')
            ->join('cmf_school', 'cmf_school.id=cmf_room.sid', 'LEFT')
            ->field('cmf_student.*')
            ->field('cmf_room.name as room_name')
            ->field('cmf_school.name as school_name')
            ->select();
        // 实例化
        $spreadsheet = new Spreadsheet();
        // 获取活动单元格
        $sheet = $spreadsheet->getActiveSheet();

        // 获取单元格
        $cellA = $sheet->getCell('A1');
        // 设置单元格的值
        $cellA->setValue('ID');
        // 设置 A 列 列宽
        $sheet->getColumnDimension('A')->setWidth(10);
        // 设置第一行 行高
        $sheet->getRowDimension(1)->setRowHeight(20);

        $cellB = $sheet->getCell('B1');
        $cellB->setValue('学生头像');
        $sheet->getColumnDimension('B')->setWidth(10);

        $cellC = $sheet->getCell('C1');
        $cellC->setValue('学生姓名');
        $sheet->getColumnDimension('C')->setWidth(10);

        $cellD = $sheet->getCell('D1');
        $cellD->setValue('学生性别');
        $sheet->getColumnDimension('D')->setWidth(10);

        $cellE = $sheet->getCell('E1');
        $cellE->setValue('学生年龄');
        $sheet->getColumnDimension('E')->setWidth(10);

        $cellE = $sheet->getCell('F1');
        $cellE->setValue('手机号');
        $sheet->getColumnDimension('F')->setWidth(10);

        $cellF = $sheet->getCell('G1');
        $cellF->setValue('身份证号');
        $sheet->getColumnDimension('G')->setWidth(30);

        $cellF = $sheet->getCell('H1');
        $cellF->setValue('所属班级');
        $sheet->getColumnDimension('H')->setWidth(30);

        $cellF = $sheet->getCell('I1');
        $cellF->setValue('修改时间');
        $sheet->getColumnDimension('I')->setWidth(30);


        $cellG = $sheet->getCell('J1');
        $cellG->setValue('创建时间');
        $sheet->getColumnDimension('J')->setWidth(40);


        // 设置样式 标题
        $styleArray = [
            'alignment' => [
                'horizontal' => 'center', //水平居中
                'vertical' => 'center', //垂直居中
            ],
            'font' => [
                'name' => '黑体',
                'bold' => false,
                'size' => 10
            ]
        ];
        // 设置样式 正文
        $styleArrayBody = [
            'alignment' => [
                'horizontal' => 'center', //水平居中
                'vertical' => 'center', //垂直居中
            ],
            'font' => [
                'name' => '宋体',
                'bold' => false,
                'size' => 10
            ]
        ];
        // 应用样式
        $sheet->getStyle('A1')->applyFromArray($styleArray);
        $sheet->getStyle('B1')->applyFromArray($styleArray);
        $sheet->getStyle('C1')->applyFromArray($styleArray);
        $sheet->getStyle('D1')->applyFromArray($styleArray);
        $sheet->getStyle('E1')->applyFromArray($styleArray);
        $sheet->getStyle('F1')->applyFromArray($styleArray);
        $sheet->getStyle('G1')->applyFromArray($styleArray);
        $sheet->getStyle('H1')->applyFromArray($styleArray);
        $sheet->getStyle('I1')->applyFromArray($styleArray);
        $sheet->getStyle('J1')->applyFromArray($styleArray);

        // 从 A2 开始填充数据
        foreach ($data as $k => $v) {
            $n = $k + 2;
            // 获取单元格
            $cellA = $sheet->getCell('A'.$n);
            // 设置单元格的值
            $cellA->setValue($v['id']);

            $cellB = $sheet->getCell('B'.$n);
            $cellB->setValue('');

            $cellC = $sheet->getCell('C'.$n);
            $cellC->setValue($v['name']);

            $cellD = $sheet->getCell('D'.$n);
            $cellD->setValue($v['sex']);

            $cellE = $sheet->getCell('E'.$n);
            $cellE->setValue($v['age']);

            $cellE = $sheet->getCell('F'.$n);
            $cellE->setValue($v['mobile']." ");

            $cellF = $sheet->getCell('G'.$n);
            $cellF->setValue($v['idcard']);

            $cellG = $sheet->getCell('H'.$n);
            $cellG->setValue($v['cid']);

            $cellG = $sheet->getCell('I'.$n);
            $cellG->setValue($v['update_time']);

            $cellG = $sheet->getCell('J'.$n);
            $cellG->setValue($v['create_time']);


            // 再给表格体设置样式
            $sheet->getStyle('A'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('B'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('C'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('D'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('E'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('F'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('G'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('H'.$n)->applyFromArray($styleArrayBody);
            $sheet->getStyle('J'.$n)->applyFromArray($styleArrayBody);
        }

        $file_name = '学生列表.xlsx';
        // 实例化导出类
        header('Content-Type:application/vnd.ms-excel');
        header('Content-Disposition:attachment;filename='.$file_name);
        header('Cache-Control:max-age=0');

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
    }

    // 从 Excel 导入数据
    public function importExcel()
    {
        // 接收文件
        $file = $_FILES['file']['tmp_name'];
        // 创建读操作
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
        // 打开文件、载入excel表格
        $spreadsheet = $reader->load($file);
        // 获取活动工作薄
        $sheet = $spreadsheet->getActiveSheet();

        // 获取总列数
        //$highestColumn = $sheet->getHighestColumn();
        // 获取总行数
        $highestRow = $sheet->getHighestRow();
        // 将列转为数字
        //$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);

        // 存放插入成功的记录
        $successLog = [];
        // 存放插入失败的记录
        $failLog = [];

        // 从第二行开始读取表格数据，循环写入数据库
        for ($i = 2; $i <= $highestRow; $i++) {
            // 第 1 次循环获取第 3 列 第 2 行单元格的值，第 2 次循环获取第 3 列 第 3 行单元格的值
            $name = $sheet->getCellByColumnAndRow(3,$i)->getValue();
            $sex = $sheet->getCellByColumnAndRow(4,$i)->getValue();
            $age = $sheet->getCellByColumnAndRow(5,$i)->getValue();
            $mobile= $sheet->getCellByColumnAndRow(6,$i)->getValue();
            $idcard= $sheet->getCellByColumnAndRow(7,$i)->getValue();
            $cid= $sheet->getCellByColumnAndRow(8,$i)->getValue();
            $data = [
                'name' => $name,
                'sex' => $sex,
                'age' => $age,
                'mobile' => $mobile,
                'idcard' => $idcard,
                'cid' => $cid,
                'update_time' => date('Y-m-d H:i:s', time()),
                'create_time' => date('Y-m-d H:i:s', time())
            ];
            $insert = Db::name('student')->insert($data);
            if ($insert) {
                $successLog[] = '第'.$i.'条，插入成功';
            } else {
                $failLog[] = '第'.$i.'条，插入失败';
            }
        }
        // 将成功数和失败数返回给 ajax
        return json(['success' => count($successLog), 'fail' => count($failLog)]);
    }
    public function download()
    {
        // 实例化
        $spreadsheet = new Spreadsheet();
        // 获取活动单元格
        $sheet = $spreadsheet->getActiveSheet();

        // 获取单元格
        $cellA = $sheet->getCell('A1');
        // 设置单元格的值
        $cellA->setValue('ID');
        // 设置 A 列 列宽
        $sheet->getColumnDimension('A')->setWidth(10);
        // 设置第一行 行高
        $sheet->getRowDimension(1)->setRowHeight(20);

        $cellB = $sheet->getCell('B1');
        $cellB->setValue('学生头像');
        $sheet->getColumnDimension('B')->setWidth(10);

        $cellC = $sheet->getCell('C1');
        $cellC->setValue('学生姓名');
        $sheet->getColumnDimension('C')->setWidth(10);

        $cellD = $sheet->getCell('D1');
        $cellD->setValue('学生性别');
        $sheet->getColumnDimension('D')->setWidth(10);

        $cellE = $sheet->getCell('E1');
        $cellE->setValue('学生年龄');
        $sheet->getColumnDimension('E')->setWidth(10);

        $cellE = $sheet->getCell('F1');
        $cellE->setValue('手机号');
        $sheet->getColumnDimension('F')->setWidth(10);


        $cellF = $sheet->getCell('G1');
        $cellF->setValue('身份证号');
        $sheet->getColumnDimension('G')->setWidth(30);

        $cellF = $sheet->getCell('H1');
        $cellF->setValue('所属班级');
        $sheet->getColumnDimension('H')->setWidth(30);

        $cellF = $sheet->getCell('I1');
        $cellF->setValue('所属学校');
        $sheet->getColumnDimension('I')->setWidth(30);


        $cellG = $sheet->getCell('J1');
        $cellG->setValue('修改时间');
        $sheet->getColumnDimension('J')->setWidth(40);

        $cellH = $sheet->getCell('K1');
        $cellH->setValue('创建时间');
        $sheet->getColumnDimension('K')->setWidth(30);

        // 设置样式 标题
        $styleArray = [
            'alignment' => [
                'horizontal' => 'center', //水平居中
                'vertical' => 'center', //垂直居中
            ],
            'font' => [
                'name' => '黑体',
                'bold' => false,
                'size' => 10
            ]
        ];
        // 设置样式 正文
        $styleArrayBody = [
            'alignment' => [
                'horizontal' => 'center', //水平居中
                'vertical' => 'center', //垂直居中
            ],
            'font' => [
                'name' => '宋体',
                'bold' => false,
                'size' => 10
            ]
        ];
        // 应用样式
        $sheet->getStyle('A1')->applyFromArray($styleArray);
        $sheet->getStyle('B1')->applyFromArray($styleArray);
        $sheet->getStyle('C1')->applyFromArray($styleArray);
        $sheet->getStyle('D1')->applyFromArray($styleArray);
        $sheet->getStyle('E1')->applyFromArray($styleArray);
        $sheet->getStyle('F1')->applyFromArray($styleArray);
        $sheet->getStyle('G1')->applyFromArray($styleArray);
        $sheet->getStyle('H1')->applyFromArray($styleArray);
        $sheet->getStyle('I1')->applyFromArray($styleArray);
        $sheet->getStyle('J1')->applyFromArray($styleArray);
        $sheet->getStyle('K1')->applyFromArray($styleArray);

        // 从 A2 开始填充数据
        // 获取单元格
        $cellA = $sheet->getCell('A2');
        // 设置单元格的值
        $cellA->setValue(1);

        $cellB = $sheet->getCell('B2');
        $cellB->setValue('');

        $cellC = $sheet->getCell('C2');
        $cellC->setValue('王晓梦');

        $cellD = $sheet->getCell('D2');
        $cellD->setValue(1);

        $cellE = $sheet->getCell('E2');
        $cellE->setValue(18);

        $cellE = $sheet->getCell('F2');
        $cellE->setValue('1');

        $cellF = $sheet->getCell('G2');
        $cellF->setValue('13247557657'." ");

        $cellF = $sheet->getCell('H2');
        $cellF->setValue('16546245');

        $cellF = $sheet->getCell('I2');
        $cellF->setValue('山东交通职业学院');

        $cellG = $sheet->getCell('J2');
        $cellG->setValue('0000-00-00');

        $cellH = $sheet->getCell('K2');
        $cellH->setValue('0000-00-00');

        // 再给表格体设置样式
        $sheet->getStyle('A2')->applyFromArray($styleArrayBody);
        $sheet->getStyle('B2')->applyFromArray($styleArrayBody);
        $sheet->getStyle('C2')->applyFromArray($styleArrayBody);
        $sheet->getStyle('D2')->applyFromArray($styleArrayBody);
        $sheet->getStyle('E2')->applyFromArray($styleArrayBody);
        $sheet->getStyle('F2')->applyFromArray($styleArrayBody);
        $sheet->getStyle('G2')->applyFromArray($styleArrayBody);
        $sheet->getStyle('H2')->applyFromArray($styleArrayBody);


        $file_name = '学生列表导入模板.xlsx';
        // 实例化导出类
        header('Content-Type:application/vnd.ms-excel');
        header('Content-Disposition:attachment;filename='.$file_name);
        header('Cache-Control:max-age=0');

        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
    }
}
